package com.dataextend.service;

import com.alibaba.fastjson.JSONObject;
import com.dataextend.dto.PropertyAndActDTO;
import com.dataextend.mapper.cluster.UserGroupMapper;
import com.dataextend.mapper.master.ClickhouseMapper;
import com.google.common.reflect.TypeToken;
import com.google.gson.Gson;
import com.google.gson.GsonBuilder;
import lombok.extern.slf4j.Slf4j;
import org.elasticsearch.action.search.SearchRequest;
import org.elasticsearch.action.search.SearchResponse;
import org.elasticsearch.client.RequestOptions;
import org.elasticsearch.client.RestHighLevelClient;
import org.elasticsearch.index.query.BoolQueryBuilder;
import org.elasticsearch.index.query.QueryBuilders;
import org.elasticsearch.search.SearchHits;
import org.elasticsearch.search.aggregations.AggregationBuilders;
import org.elasticsearch.search.aggregations.bucket.terms.Terms;
import org.elasticsearch.search.aggregations.bucket.terms.TermsAggregationBuilder;
import org.elasticsearch.search.builder.SearchSourceBuilder;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;

import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 *   用户人群分析模型
 */
@Slf4j
@Service
public class UserGroupAnalysisServiceV2 {
    @Autowired
    RestHighLevelClient restHighLevelClient;
    @Value("${usertag_all.usertag_all.index}")
    private String usertagIndex;
    @Value("${usertag_all.usertag_all.source_field}")
    private String usertagSourceField;
    @Value("${usertag_all.usertag_all.type}")
    private String type;
    @Autowired
    private UserProfileService userProfileService;
    @Autowired
    private UserGroupMapper userGroupMapper;
    @Autowired
    private ClickhouseMapper clickhouseMapper;


    /**
     * 属性规则
     */
    private List<JSONObject> propertyRules (PropertyAndActDTO.propertyRules propertyRules, String dimension) {
        Pattern p = Pattern.compile(".*\\d+.*");
        String relationship = propertyRules.getRelationship();
        List<String> sqlArray = new ArrayList<>();
        for (PropertyAndActDTO.ruleDetails details : propertyRules.getRuleDetails()) {
            String key = details.getKey();
            String value = details.getValue();
            String condition = details.getCondition();
            // 判断value值是否是数字比较大小
            Matcher m = p.matcher(value);
            String sqlInfo = "";
            if (m.matches()) {
                sqlInfo = "cast(" + key + " as Double) " + condition + " cast(" + value + " as Double)";
            } else {
                sqlInfo = key + " " + condition + " '" + value + "' ";
            }
            sqlArray.add(sqlInfo);
        }
        String sqlResult = "select `" + dimension + "` as dimension, count(distinct userid) as value from userprofile_info where " + String.join(" " + relationship + " ",sqlArray) + " group by `" + dimension + "`";
        log.info(">>>>属性规则SQL:" + sqlResult);
        List<JSONObject> resultDetail = clickhouseMapper.userGroupAnalysisByAct(sqlResult);
        return resultDetail;
    }


    /**
     * 行为规则
     */
    private List<JSONObject> actRules(PropertyAndActDTO.actRules actRules, String dimension) {
        String clickhouseSql = "";
        if (actRules.getRelationship().equals("and")) {
            // 行为规则之间是且关系
            int i=1;
            List<String> sqlArray = new ArrayList<>();
            for (PropertyAndActDTO.actDetails rule : actRules.getActDetails()) {
                String sqlDetail = "";
                String sqlDetail1 = "(select distinct_id,count(*) as value from userprofile_analysis_model ";
                String sqlDetail2 = "where clickevent" + rule.getActType() + "'" + rule.getEventCode() + "' and datadate>='" ;
                String sqlDetail3 = rule.getStartDate() + "' and datadate<='" + rule.getEndDate() + "' ";
                String sqlDetail4 = "group by distinct_id having count(*) >=" + rule.getNum() + ") t" + i;
                if (i>1) {
                    String sqlDetail5 = "  on t" + (i-1)+ ".distinct_id = t" + i + ".distinct_id";
                    sqlDetail = sqlDetail1 + sqlDetail2 + sqlDetail3 + sqlDetail4 + sqlDetail5;
                } else {
                    sqlDetail = sqlDetail1 + sqlDetail2 + sqlDetail3 + sqlDetail4;
                }
                sqlArray.add(sqlDetail);
                i+=1;
            }
            clickhouseSql = "select tt.`"+dimension + "` as dimension, count(distinct tt.userid) as value from " + String.join(" inner join ", sqlArray) +
                      " inner join userprofile_info tt on t1.distinct_id = tt.userid group by tt.`" + dimension + "`";
        } else {
            // 行为规则之间是或关系
            List<String> sqlArray = new ArrayList<>();
            for (PropertyAndActDTO.actDetails rule : actRules.getActDetails()) {
                String sqlDetail1 = "select distinct_id,count(*) as value  from userprofile_analysis_model ";
                String sqlDetail2 = "where clickevent" + rule.getActType() + "'" + rule.getEventCode() + "' and datadate>='" ;
                String sqlDetail3 = rule.getStartDate() + "' and datadate<='" + rule.getEndDate() + "' ";
                String sqlDetail4 = "group by distinct_id having count(*) >=" + rule.getNum() ;
                String sqlDetail = sqlDetail1 + sqlDetail2 + sqlDetail3 + sqlDetail4;
                sqlArray.add(sqlDetail);
            }
            clickhouseSql = "select tt.`" + dimension + "` as dimension, count(distinct t.distinct_id) as value from(" + String.join(" union all ", sqlArray) + " )  t " +
            " inner join userprofile_info tt on t.distinct_id = tt.userid group by tt.`" + dimension + "`";
        }
        log.info(">>>>行为规则SQL:" + clickhouseSql);
        List<JSONObject> resultDetail = clickhouseMapper.userGroupAnalysisByAct(clickhouseSql);
        return resultDetail;
    }


    /**
     * 属性规则 + 行为规则
     */
    private List<JSONObject> allRules(PropertyAndActDTO.propertyRules property, PropertyAndActDTO.actRules act, String dimension) {
        // 查询SQL
        String clickhouseSQL = "";

        // 属性SQL拼接
        String propertySQL = "";
        Pattern p = Pattern.compile(".*\\d+.*");
        String relationship = property.getRelationship();
        List<String> propertySqlArray = new ArrayList<>();
        for (PropertyAndActDTO.ruleDetails details : property.getRuleDetails()) {
            String key = details.getKey();
            String value = details.getValue();
            String condition = details.getCondition();
            Matcher m = p.matcher(value);  // 判断value值是否是数字比较大小
            String sqlInfo = "";
            if (m.matches()) {
                sqlInfo = "cast(" + key + " as Double) " + condition + " cast(" + value + " as Double)";
            } else {
                sqlInfo = key + " " + condition + " '" + value + "' ";
            }
            propertySqlArray.add(sqlInfo);
        }
        propertySQL = "select userid from userprofile_info where " + String.join(" " + relationship + " ",propertySqlArray);
        log.info(">>>>>属性SQL拼接:" + propertySQL);

        //  行为SQL拼接
        String actSQL = "";
        if (act.getRelationship().equals("and")) {
            //  行为规则之间是且关系
            int i=1;
            List<String> sqlArray = new ArrayList<>();
            for (PropertyAndActDTO.actDetails rule : act.getActDetails()) {
                String sqlDetail = "";
                String sqlDetail1 = "(select distinct_id,count(*) as value from userprofile_analysis_model ";
                String sqlDetail2 = "where clickevent" + rule.getActType() + "'" + rule.getEventCode() + "' and datadate>='" ;
                String sqlDetail3 = rule.getStartDate() + "' and datadate<='" + rule.getEndDate() + "' ";
                String sqlDetail4 = "group by distinct_id having count(*) >=" + rule.getNum() + ") t" + i;
                if (i>1) {
                    String sqlDetail5 = "  on t" + (i-1)+ ".distinct_id = t" + i + ".distinct_id";
                    sqlDetail = sqlDetail1 + sqlDetail2 + sqlDetail3 + sqlDetail4 + sqlDetail5;
                } else {
                    sqlDetail = sqlDetail1 + sqlDetail2 + sqlDetail3 + sqlDetail4;
                }
                sqlArray.add(sqlDetail);
                i+=1;
            }
            actSQL = "select t1.distinct_id from " + String.join(" inner join ", sqlArray) + " group by t1.distinct_id";
        } else {
            //  行为规则之间是或关系
            List<String> sqlArray = new ArrayList<>();
            for (PropertyAndActDTO.actDetails rule : act.getActDetails()) {
                String sqlDetail1 = "select distinct_id,count(*) as value  from userprofile_analysis_model ";
                String sqlDetail2 = "where clickevent" + rule.getActType() + "'" + rule.getEventCode() + "' and datadate>='" ;
                String sqlDetail3 = rule.getStartDate() + "' and datadate<='" + rule.getEndDate() + "' ";
                String sqlDetail4 = "group by distinct_id having count(*) >=" + rule.getNum() ;
                String sqlDetail = sqlDetail1 + sqlDetail2 + sqlDetail3 + sqlDetail4;
                sqlArray.add(sqlDetail);
            }
            actSQL = "select t.distinct_id from(" + String.join(" union all ", sqlArray) + " )  t group by t.distinct_id";
        }
        log.info(">>>>>行为SQL拼接:" + actSQL);

        clickhouseSQL = "select a3.`" + dimension + "` as dimension, count(distinct a1.userid) as value from (" + propertySQL + " ) a1 inner join (" + actSQL
                + ") a2 on a1.userid=a2.distinct_id" + " inner join userprofile_info a3  on a1.userid = a3.userid group by a3.`" + dimension + "`";
        log.info(">>>>>clickhouseSQL:" + clickhouseSQL);
        List<JSONObject> resultDetail  = clickhouseMapper.userGroupAnalysisByAct(clickhouseSQL);
        return resultDetail;
    }



    public List<JSONObject> parseRule(String dimension, String groupId) {
        List<JSONObject> resultDetail = new ArrayList<>();
        JSONObject groupInfo = userGroupMapper.getGroupInfo(groupId);
        String ruleType = groupInfo.getString("rule_type");
        String propertyRules = groupInfo.getString("property_rules");
        String actRules = groupInfo.getString("act_rules");
        switch (ruleType) {
            // 用户属性
            case "property" :
                Gson gson1 = new GsonBuilder().disableHtmlEscaping().create();
                PropertyAndActDTO.propertyRules property = gson1.fromJson(propertyRules, new TypeToken<PropertyAndActDTO.propertyRules>(){}.getType());
                resultDetail = propertyRules(property, dimension);
                break;
            // 用户行为
            case "act":
                Gson gson2 = new GsonBuilder().disableHtmlEscaping().create();
                PropertyAndActDTO.actRules act = gson2.fromJson(actRules, new TypeToken<PropertyAndActDTO.actRules>(){}.getType());
                resultDetail =actRules(act, dimension);
                break;
            // 用户属性+行为
            case "all":
                Gson gson3 = new GsonBuilder().disableHtmlEscaping().create();
                PropertyAndActDTO.propertyRules property_ = gson3.fromJson(propertyRules, new TypeToken<PropertyAndActDTO.propertyRules>(){}.getType());
                Gson gson4 = new GsonBuilder().disableHtmlEscaping().create();
                PropertyAndActDTO.actRules act_ = gson4.fromJson(actRules, new TypeToken<PropertyAndActDTO.actRules>(){}.getType());
                resultDetail =allRules(property_, act_, dimension);
                break;
        }

        log.info(">>>resultDetail =:" + resultDetail );
        return resultDetail;
    }


    public List<JSONObject> dimensionAnalysis (String dimension, String groupId){
        String dimensionName = userGroupMapper.getDimesionName(dimension);
        List<JSONObject> resultOrigin = parseRule(dimensionName, groupId);
        return resultOrigin;
    }



}
